package belf.migrate.engine.ddl.procedure.oracle;

import belf.migrate.api.model.QualifiersModel;
import belf.migrate.api.table.schema.Catalog;
import belf.migrate.api.util.QuoteType;
import belf.migrate.engine.ddl.BaseDDLConvert;
import belf.migrate.engine.ddl.helper.oracle.OracleAntlrBuilder;
import belf.migrate.plugin.oracle.antlr.PlSqlLexer;
import belf.migrate.plugin.oracle.antlr.PlSqlParser;
import belf.migrate.plugin.oracle.antlr.PlSqlParserBaseListener;
import org.antlr.v4.runtime.CharStream;
import org.antlr.v4.runtime.CharStreams;
import org.antlr.v4.runtime.CommonTokenStream;
import org.antlr.v4.runtime.tree.ParseTree;
import org.antlr.v4.runtime.tree.ParseTreeWalker;
import org.antlr.v4.runtime.tree.TerminalNode;
import org.antlr.v4.runtime.tree.TerminalNodeImpl;

import java.util.ArrayList;
import java.util.Arrays;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

public class Oracle2DMProcedureConvert extends BaseDDLConvert {

    public Oracle2DMProcedureConvert(String objectName, String sql, Catalog sourceCatalog, Catalog sinkCatalog,
                                     String schema, QualifiersModel qualifiersModel) {
        super(objectName, sql, sourceCatalog, sinkCatalog, schema, qualifiersModel);
    }

    /**
     * 先获取以下信息：
     * <ul>
     * <li>节点"create_procedure_body"下的存储过程名称、输入参数，</li>
     * <li>节点"seq_of_declare_specs"下的“declare_spec”局部变量，</li>
     * <li>BEGIN...END中间的“statement”语句</li>
     * <li>END后的过程结尾标签</li>
     * </ul>
     * 再拼接转换后的SQL语句
     *
     * @return
     */
    @Override
    public String convert() {
        Pattern pattern = Pattern.compile("\\sEDITIONABLE\\s", Pattern.CASE_INSENSITIVE);
        Matcher matcher = pattern.matcher(sql);
        String replacedSql = matcher.replaceAll(" ");
        CharStream input = CharStreams.fromString(replacedSql);

        // 创建词法分析器和解析器
        PlSqlLexer lexer = new PlSqlLexer(input);
        CommonTokenStream tokens = new CommonTokenStream(lexer);
        PlSqlParser parser = new PlSqlParser(tokens);

        // 从解析器中获取起始规则并进行解析
        PlSqlParser.Sql_scriptContext tree = parser.sql_script();
        OracleProcedureVistor visitor = new OracleProcedureVistor(this,
                QuoteType.DOUBLE_QUOTE.getValue(),
                schema,
                funcMapping,
                typeMapping);
        // 遍历树找关键节点
        visitor.visit(tree);

        String ddl = String.format("CREATE OR REPLACE PROCEDURE %s(%s) AS %s \nBEGIN \n%s \nEND %s\s;",
                visitor.getProcedureName(),
                String.join(",", buildParameters(visitor.getParameters())),
                buildDeclareVariables(visitor.getVariables()),
                String.join("\n", buildProcedureSqls(visitor, visitor.getProcedureSqls())),
                visitor.getProcedureLableName()
        );
        return ddl;
//        return SqlFormatter.of(Dialect.PlSql).format(ddl);
    }


    private List<String> buildParameters(List<PlSqlParser.ParameterContext> parameters) {
        List<String> list = new ArrayList<>();
        for (PlSqlParser.ParameterContext parameter : parameters) {
            String parameterName = parameter.parameter_name().getText().trim();
            String dataType = parameter.type_spec().getText().toUpperCase().trim();
            String direction = "";
            for (ParseTree child: parameter.children) {
                if (child instanceof TerminalNodeImpl) {
                    direction = child.getText().trim();
                }
            }
            dataType = dataType.replace("UNSIGNED", "");
            if (typeMapping.containsKey(dataType)) {
                dataType = typeMapping.get(dataType).getTo();
            }
            list.add(parameterName + " " + direction + " " + dataType);
        }
        return list;
    }

    private String buildDeclareVariables(List<PlSqlParser.Declare_specContext> variables) {
        String result = "";
        if (!variables.isEmpty()) {
            List<String> list = new ArrayList<>();
            for (PlSqlParser.Declare_specContext variable : variables) {
                // 找到普通变量或申明为类型的变量
                if (variable != null) {
                    Declare_specListener listener = new Declare_specListener();
                    ParseTreeWalker walker = new ParseTreeWalker();
                    walker.walk(listener, variable);
                    list.add(listener.toString());
                }
            }
            result = "\n" + String.join("\n", list) ;
        }
        return result;
    }

    // 用于解析申明参数“declare_spec”节点的监听器，拼凑为美观的申明语句
    private class Declare_specListener extends PlSqlParserBaseListener {
        /** sb.append()时，这些符号前不加空格 */
        public static List<String> TRIMLEFT_SYMBOLS = Arrays.asList("'", "(", ")", "\"", ".", ",", ";");
        private StringBuilder sb = new StringBuilder();

        @Override
        public void visitTerminal(TerminalNode node) {
            String text = node.getText().trim();
            if (hasAncestor("DatatypeContext", node)) {
                String dataType = text.toUpperCase();
                if (typeMapping.containsKey(dataType)) {
                    text = typeMapping.get(dataType).getTo();
                }
            }

            if (TRIMLEFT_SYMBOLS.contains(text)) {
                OracleAntlrBuilder.appendTrimLeft(sb, text);
            } else {
                sb.append(text + " ");
            }
        }

        @Override
        public String toString() {
            return sb.toString();
        }
    }

    private List<String> buildProcedureSqls(OracleProcedureVistor visitor, List<PlSqlParser.StatementContext> procedureSqls) {
        List<String> list = new ArrayList<>();
        for (PlSqlParser.StatementContext procedureSql : procedureSqls) {
            StringBuilder sb = new StringBuilder();
            OracleAntlrBuilder builder = new OracleAntlrBuilder(this, sb, qualifiersModel, schema);
            StatementListener listener = new StatementListener(visitor, builder);
            ParseTreeWalker walker = new ParseTreeWalker();
//            System.out.println(procedureSql.getText());
            walker.walk(listener, procedureSql);
            String statement = builder.toSql().trim();
            if (!statement.endsWith(";")) {
                statement += ";";
            }
            list.add(statement);
        }
        return list;
    }

    private class StatementListener extends PlSqlParserBaseListener {
        private OracleProcedureVistor visitor;
        private OracleAntlrBuilder builder;
        public StatementListener(OracleProcedureVistor visitor, OracleAntlrBuilder builder) {
            this.builder = builder;
            this.visitor = visitor;
        }

        @Override
        public void visitTerminal(TerminalNode node) {
            // TODO：进行函数替换、语法替换等操作

            builder.output(node);

        }
    }

}
